package com.yangjun.dao.impl.businessAnalysis.memberAnalysis.logisticsAnalysis.packageEfficacy;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.yangjun.common.DVConstants;
import com.yangjun.dao.mybatis2.mapper.common.CommonMapper;

@Repository
public class PackageEfficacyDaoImpl {
	
	 @Autowired
	 @Resource(name = "bikadJdbcTemplate")
	 private JdbcTemplate mJdbcTemplate;
	 
	 @Autowired
	 public CommonMapper mapper;
	 
	 
	 
	 
	 /**
	  * 获取平台
	  * @return
	  */
	 public List<Map<String,Object>> findSingleSelectPlatform(){
//	    	List<String> siteidWeight = Arrays.asList(DVConstants.SITEID_WEIGHT);
	    	List<String> siteidWeight = mapper.lastHaveSalesPlatform();
	    	List<Map<String,Object>> resultMap = Lists.newArrayList();
	    	Map<String,Map<String,Object>> idMap = Maps.newHashMap();
//	    	List<Map<String,Object>> siteMap =  mJdbcTemplate.queryForList("select a2.type_id as ordersource,a2.type_name as sourcename from bidev.dim_kad_values a2 where a2.column_name='origin' and a2.table_name='dw_kad_orderitem'  AND nvl(a2.status,1)=1");
	    	List<Map<String,Object>> siteMap =  mJdbcTemplate.queryForList("select values_id as ordersource,values_name as sourcename from  bidev.dim_kad_base_values t where t.values_source='ordersource'");
	    	for(Map<String,Object> map : siteMap){
				String id = String.valueOf(map.get("ordersource"));
	    		idMap.put(id, map);
			}
	    	for(String str : siteidWeight){
	    		resultMap.add(idMap.get(str));
	    		idMap.remove(str);
	    	}
	    	for(Map.Entry<String,Map<String,Object>> map:  idMap.entrySet()){
	        	resultMap.add(map.getValue());
	    	}
	    	return resultMap;
	    }
	 
	 /**
	 * 包裹数 
	 * @param ordersourcecode
	 * @param urgenttype
	 * @param column
	 * @param hourOrday hour 小时折线图图  day 按天折线图
	 * @param day 1 表示当天  7表示最近7天
	 * @return
	 */
	 public List<Map<String,Object>> packageNumber(String ordersourcecode,int urgenttype,String column,String hourOrday,int day){
		 
		 /**
		  * 
		  * sys_cons_qty '同步-包裹数';
			sys_cons_sku '同步-包裹行目数';
			build_cons_qty '生成拣单-包裹数';
			build_cons_sku '生成拣单-包裹行目数';
			pks_cons_qty '拣货开中-包裹数';
			pkn_cons_qty'待拣货-包裹数';
			pkn_cons_sku'待拣货-包裹行目数';
			pke_cons_qty'拣货完成-包裹数';
			pke_cons_sku'拣货完成-包裹行目数';
			reck_cons_qty'复核-包裹数';
			reck_cons_sku'复核-行目数';
			reck_pend_cons_qty'复核-待复核包裹数';
			reck_unpass_cons_qty'复核-复核失败包裹数';
			weight_cons_qty'称重-包裹数';
			weight_pend_cons_qty'称重-待称重包裹数';
			collect_cons_qty'揽收-包裹数';
			gather_fail_cons_qty'集货失败-包裹数';
			gather_fail_sku'集货失败-行目数';
			gather_fail_ware_qty'集货失败-商品数';
		  * 
		  */
		 List<Object> params = new ArrayList<Object>();
		 
		 StringBuffer sql = null;
//		 String hour_day = "";
		 if("hour".equals(hourOrday)){
//			 hour_day = "dt.data_date_h";visp_dim_serial_day_h可以被visc_cons_time_dim_h替换
			 sql = new StringBuffer("select dt.data_date_h as hour_day, "
					 +"       pkg_visual_consign.util_division(sum(t."+column+"), "
					 +"                                        count(distinct t.data_date), "
					 +"                                        0) as hour_day_value "
					 +"  from visp_dim_serial_day_h dt,visc_cons_node_perf_h t "
					 +"  where 1=1  and dt.data_date=t.data_date(+) and dt.data_date_h=t.data_h(+) "
					 );
		 }else if("day".equals(hourOrday)){
//		     hour_day = "to_char(dt.data_date, 'mm-dd')";
		     sql = new StringBuffer("select to_char(data_date, 'mm-dd') as hour_day, "
					 +"       pkg_visual_consign.util_division(sum(t."+column+"), "
					 +"                                        count(distinct t.data_date), "
					 +"                                        0) as hour_day_value "
					 +"  from visc_cons_node_perf_d t "
					 +"  where t.data_type = 0"
					 );
		 }
		 
//		  sql = new StringBuffer("select "+hour_day+" as hour_day, "
//				 +"       pkg_visual_consign.util_division(sum(t."+column+"), "
//				 +"                                        count(distinct t.data_date), "
//				 +"                                        0) as hour_day_value "
//				 +"  from visp_dim_serial_day_h dt, visc_cons_node_perf_h t "
//				 +"  where dt.data_date = t.data_date(+) "
//				 +"  and dt.data_date_h = t.data_h(+) "
//				 );
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and t.ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and t.urgenttype=? ");
			 params.add(urgenttype);
		 }
		 
		 if("hour".equals(hourOrday)){
			 if(day==7){
				 sql.append("and dt.data_date between trunc(sysdate - 6) and trunc(sysdate) ");
			 }else if(day==1){
				 sql.append("and dt.data_date = trunc(sysdate) ");
			 }
			 sql.append("group by data_date_h order by data_date_h ");
		 }else if("day".equals(hourOrday)){
			 //如果按天统计则是近30天
			 sql.append("and data_date between trunc(sysdate - 29) and trunc(sysdate) ");
		     sql.append("group by data_date order by data_date ");	 
//		     System.err.println("packageNumber--"+sql.toString());
		 }
		 
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
		 
	 }
	 
	 
	 public Integer overviewFormToSynchronized(String platform,String urgenttype){
		 
		 List<Object> params = new ArrayList<Object>();
		 StringBuffer sql = new StringBuffer( "select b.unsys_order_qty "
				    +"  from (select nvl(a.ordersource, -1) ordersourcecode, "
				    +"               nvl(a.urgenttype, '-1') urgenttype, "
				    +"               count(distinct a.ordercode) - count(distinct b.ordercode) as unsys_order_qty "
				    +"          from om.om_order a, "
				    +"               wms.wl_consign b, "
				    +"               table(birpt.get_parm_dim('ordersource')) p1 "
				    +"         where a.ordercode = b.ordercode(+) "
				    +"           and a.orderstatus <> '10' "
				    +"           and a.ordersource = p1.key(+) "
				    +"           and a.createdate between trunc(sysdate) and "
				    +"               trunc(sysdate + 1) - 1 / 86400 "
				 );
		 
		 if(!platform.equals("-1")){
			 sql.append(" and a.ordersource = "+platform+"");
//			 params.add(platform);
		 }
		 if(!urgenttype.equals("-1")){
			 sql.append(" and a.urgenttype ="+urgenttype+" ");
//			 params.add(urgenttype);
		 }
		 
		 sql.append(" group by cube(a.ordersource, a.urgenttype)) b where b.ordersourcecode = "+platform+" and b.urgenttype = "+urgenttype+"");
		 
		 System.err.println("overviewFormToSynchronized-->"+sql.toString());
		 
//		 Map<String, Object> queryForMap = mJdbcTemplate.queryForMap(sql.toString(), params.toArray());
		 List<Map<String, Object>> queryForList = mJdbcTemplate.queryForList(sql.toString());
		 
		 int returnValue = 0;
		 if(queryForList!=null && queryForList.size()>0){
			 for (Map<String, Object> map : queryForList) {
				 returnValue = Integer.parseInt((map.get("unsys_order_qty").toString()));
			}
		 }
		 
		 return returnValue;
	 }
	 
	 /**
	  * 近7天均值
	  * @param ordersourcecode
	  * @param urgenttype
	  * @param column
	  * @return
	  */
	 public Float packageNumberAvgLast7(String ordersourcecode,int urgenttype,String column){
		 
		 List<Object> params = new ArrayList<Object>();
		 //visp_dim_serial_day_h可以被visc_cons_time_dim_h替换
		 StringBuffer sql = new StringBuffer("select  "
				 +"       pkg_visual_consign.util_division(sum(t."+column+"), "
				 +"                                        count(distinct t.data_date), "
				 +"                                        2) as avg_last7_value "
				 +"  from visp_dim_serial_day_h dt, visc_cons_node_perf_h t "
				 +"  where dt.data_date = t.data_date(+) "
				 +"  and dt.data_date_h = t.data_h(+) "
				 +"  and dt.data_date between trunc(sysdate - 7) and trunc(sysdate-1) "
				 );
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and t.ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and t.urgenttype=? ");
			 params.add(urgenttype);
		 }
//		 System.err.println("packageNumberAvgLast7--"+sql.toString());
		 
		 Map<String, Object> queryForMap = mJdbcTemplate.queryForMap(sql.toString(), params.toArray());
		 
		 return Float.parseFloat(queryForMap.get("avg_last7_value").toString());
		 
	 }
	 
	
	 
	 /**
	  * 包裹时效--近30天
	  * @param ordersourcecode
	  * @param urgenttype
	  * @param fromColumn
	  * @param toColumn
	  * @return
	  */
	 public List<Map<String,Object>> packageEffectiveness(String ordersourcecode,int urgenttype,String fromColumn,String toColumn){
		 
		 /**
		  * shitf+ctr+a 列选中
			(sum(clock_sysn_weight),sum(weight_cons_qty)) as sysn_weit_perf,--同步到称重
			(sum(clock_order_sysn),sum(sys_cons_qty)) as order_sysn_perf,--订单到同步
			(sum(clock_sysn_build),sum(build_cons_qty)) as sysn_build_perf,--同步到创建拣货单
			(sum(c.clock_build_pks),sum(pks_cons_qty)) as build_pks_perf,--创建到拣货开始
			(sum(c.clock_pks_pke),sum(pke_cons_qty)) as pks_pke_perf,--拣货开始到结束
			(sum(c.clock_pke_reck),sum(reck_cons_qty)) as pke_reck_perf,--拣货结束到复核
			(sum(c.clock_reck_weight),sum(weight_cons_qty)) as reck_weight_perf,--复核到称重,
			(sum(c.clock_weight_collect),sum(collect_cons_qty)) as weight_collect_perf,--称重到揽收
		  * 
		  */
	
	
		List<Object> params = new ArrayList<Object>();
		 //visp_dim_serial_day 可以被visc_cons_time_dim_d替换
		 StringBuffer sql = new StringBuffer("select /*+parallel(2)+*/ to_char(dt.data_date, 'mm-dd') as data_date, "
				 +"       pkg_visual_consign.util_division(sum("+fromColumn+"), "
				 +"                                        sum("+toColumn+"), "
				 +"                                        2) as clock_qty_rate  "
				 +"   from visp_dim_serial_day dt, visc_cons_node_perf_d t "
				 +"   where dt.data_date = t.data_date(+) "
				 +"   and t.data_type(+) =0  "
				 +"   and dt.data_date between trunc(sysdate - 29) and trunc(sysdate) "
				 );
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and t.ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and t.urgenttype=? ");
			 params.add(urgenttype);
		 }
		 
		 sql.append("group by dt.data_date order by dt.data_date");
		 System.err.println("packageEffectiveness--"+sql.toString());
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
		 
	 }
	 
	 
	 /**
	  * 包裹时效--近7天均值
	  * @param ordersourcecode
	  * @param urgenttype
	  * @param fromColumn
	  * @param toColumn
	  * @return
	  */
	 public Float packageEffectivenessAvgLast7(String ordersourcecode,int urgenttype,String fromColumn,String toColumn){
		 
		 List<Object> params = new ArrayList<Object>();
		 
		 StringBuffer sql = new StringBuffer("select  "
				 +"               pkg_visual_consign.util_division(sum("+fromColumn+"), "
				 +"                                                sum("+toColumn+"),2) as clock_qty_avg "
				 +"          from visc_cons_node_perf_d t "
				 +"         where t.data_type = 0"
				 +"           and t.data_date between trunc(sysdate - 7) and trunc(sysdate-1) "
				 );
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and t.ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and t.urgenttype=? ");
			 params.add(urgenttype);
		 }
		 
//		 System.err.println("packageEffectivenessAvgLast7"+sql.toString());
		 
		 Map<String, Object> queryForMap = mJdbcTemplate.queryForMap(sql.toString(), params.toArray());
		 
		 return Float.parseFloat(queryForMap.get("clock_qty_avg").toString());
		 
	 }
	 
	 
	 
	 /**
	  * 快递公司时效 -- 近7日
	  * @param ordersourcecode
	  * @param urgenttype
	  * @param province
	  * @param city
	  * @param district
	  * @return
	  */
	 public List<Map<String,Object>> expressCompanyEffectiveness(String ordersourcecode,int urgenttype,String province,String city,String district){
	
		List<Object> params = new ArrayList<Object>();
		 
		 StringBuffer sql = new StringBuffer("select t.transfername, "
				 +"       pkg_visual_consign.util_division(sum(clock_collect_sign), "
				 +"                                        sum(sign_cons_qty)) as clock_qty_rate "
				 +"   from VISC_EXPRESS_PERF t "
				 +"   where 1=1 "
				 );
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and t.ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and t.urgenttype=? ");
			 params.add(urgenttype);
		 }
		 if(StringUtils.isNotBlank(province)){
			 sql.append("and t.province=? ");
			 params.add(province);
		 }
		 if(StringUtils.isNotBlank(city)){
			 sql.append("and t.city=? ");
			 params.add(city);
		 }
		 if(StringUtils.isNotBlank(district)){
			 sql.append("and t.district=? ");
			 params.add(district);
		 }
		 sql.append("group by t.transfername order by clock_qty_rate desc");
//		 System.err.println(sql.toString());
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
		 
	 }
	 
	 /**
	  * 包裹占比--近7日
	  * @param ordersourcecode
	  * @param urgenttype
	  * @param province
	  * @param city
	  * @param district
	  * @return
	  */
	 public List<Map<String,Object>> packageRate(String ordersourcecode,int urgenttype,String province,String city,String district){
		 
		 List<Object> params = new ArrayList<Object>();
		 
		 StringBuffer sql = new StringBuffer("select t.transfername, "
				 +"       sum(sign_cons_qty) sign_cons_qty "
				 +"   from VISC_EXPRESS_PERF t "
				 +"   where 1=1 "
				 );
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and t.ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and t.urgenttype=? ");
			 params.add(urgenttype);
		 }
		 if(StringUtils.isNotBlank(province)){
			 sql.append("and t.province=? ");
			 params.add(province);
		 }
		 if(StringUtils.isNotBlank(city)){
			 sql.append("and t.city=? ");
			 params.add(city);
		 }
		 if(StringUtils.isNotBlank(district)){
			 sql.append("and t.district=? ");
			 params.add(district);
		 }
		 sql.append("group by t.transfername order by sign_cons_qty desc");
//		 System.err.println(sql.toString());
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
		 
	 }
	 
	 /**
	  * 包裹--> 重量分布 -->包裹数
	  * @param ordersourcecode
	  * @param urgenttype
	  * @param transfername
	  * @param province
	  * @param city
	  * @param district
	  * @return
	  */
	 public List<Map<String,Object>> weightDistribute(String ordersourcecode,int urgenttype,String transfername,String province,String city,String district){
		 
		 List<Object> params = new ArrayList<Object>();
		 
		 StringBuffer sql = new StringBuffer("select t.wei_type ,sum(t.qty) as qty from visc_cons_weight_distribut t "
				 +"where t.data_date between trunc(sysdate - 6) and trunc(sysdate) "
				 );
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and t.ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and t.urgenttype=? ");
			 params.add(urgenttype);
		 }
		 if(StringUtils.isNotBlank(province)){
			 sql.append("and t.province=? ");
			 params.add(province);
		 }
		 if(StringUtils.isNotBlank(city)){
			 sql.append("and t.city=? ");
			 params.add(city);
		 }
		 if(StringUtils.isNotBlank(district)){
			 sql.append("and t.district=? ");
			 params.add(district);
		 }
		 if(StringUtils.isNotBlank(transfername)){
			 sql.append("and t.transfername=? ");
			 params.add(transfername);
		 }
		 sql.append("group by t.wei_type ,t.wei_type_code order by t.wei_type_code");
//		 System.err.println(sql.toString());
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
		 
	 }
	 
	 /**
	  * 包裹-->重量分布-->点击千克
	  * @param ordersourcecode
	  * @param urgenttype
	  * @param transfername
	  * @param province
	  * @param city
	  * @param district
	  * @param wei_type
	  * @return
	  */
	 public List<Map<String,Object>> weightDistributeByWeitype(String ordersourcecode,int urgenttype,String transfername,String province,String city,String district,String wei_type){
		 
		 List<Object> params = new ArrayList<Object>();
		 
		 StringBuffer sql = new StringBuffer("select to_char(t.data_date, 'mm-dd') as data_date, "
				 +"       sum(decode(wei_type, '"+wei_type+"', qty, 0)) as qty, "
				 +"       pkg_visual_consign.util_division(sum(decode(wei_type, '"+wei_type+"', qty, 0)), sum(qty),4)as rate "
				 +"  from visc_cons_weight_distribut t "
				 +"  where data_date between  trunc(sysdate - 31) and  trunc(sysdate - 1) ");
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and urgenttype=? ");
			 params.add(urgenttype);
		 }
		 if(StringUtils.isNotBlank(province)){
			 sql.append("and province=? ");
			 params.add(province);
		 }
		 if(StringUtils.isNotBlank(city)){
			 sql.append("and city=? ");
			 params.add(city);
		 }
		 if(StringUtils.isNotBlank(district)){
			 sql.append("and district=? ");
			 params.add(district);
		 }
		 if(StringUtils.isNotBlank(transfername)){
			 sql.append("and transfername=? ");
			 params.add(transfername);
		 }
		 sql.append(" group by t.data_date order by t.data_date");
//		 System.err.println("包裹-->重量分布-->点击千克"+sql.toString());
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
		 
	 }
	 
	 
	 
	 
	 /**
	  * 近30天包裹出库
	  * @param packagetype 包裹类型 0普通，1紧急
	  * @param transfername 快递公司中文  
	  * @param province 省
	  * @param city 市
	  * @return
	  */
	 public List<Map<String,Object>> last30DayPackageOutbound(String ordersourcecode,int urgenttype,String transfername,String province,String city,String district){
		 List<Object> params = new ArrayList<Object>();
		 StringBuffer sql = new StringBuffer("select to_char(data_date, 'mm-dd') as date_day,sum(qty) as qty from visc_cons_type  "
				 +"where data_date between sysdate-31 and sysdate - 1 ");
		 
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and ordersourcecode=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and urgenttype=? ");
			 params.add(urgenttype);
		 }
		 if(StringUtils.isNotBlank(transfername)){
			 sql.append("and transfername=? ");
			 params.add(transfername);
		 }
		 if(StringUtils.isNotBlank(province)){
			 sql.append("and province=? ");
			 params.add(province);
		 }
		 if(StringUtils.isNotBlank(city)){
			 sql.append("and city=? ");
			 params.add(city);
		 }
		 if(StringUtils.isNotBlank(district)){
			 sql.append("and district=? ");
			 params.add(district);
		 }
		 sql.append("group by data_date order by data_date");
		 
//		 System.err.println(sql.toString());
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
	 }
	 
	 /**
	  * urgenttype 包裹类型 0普通，1紧急
	  * drugtype 1->药品 2->非药品 3->混合
	  * @param urgenttype
	  * @param transfername
	  * @param province
	  * @param city
	  * @return
	  */
	 public List<Map<String,Object>> last30DayPackageOutboundDrugtype(String ordersourcecode,int urgenttype,int drugtype,String transfername,String province,String city,String district){
		 List<Object> params = new ArrayList<Object>();
		 //visp_dim_serial_day 可以被visc_cons_time_dim_d替换
		 StringBuffer sql = new StringBuffer("select to_char(dt.data_date, 'mm-dd') as date_day,drugtypecn,nvl(sum(qty),0) as qty from visp_dim_serial_day dt,visc_cons_type t  "
				 +"where dt.data_date = t.data_date(+) and dt.data_date between sysdate-31 and sysdate - 1 ");
		 
		 if(StringUtils.isNotBlank(ordersourcecode)){
			 sql.append("and ordersourcecode(+)=? ");
			 params.add(ordersourcecode);
		 }
		 if(urgenttype==1 || urgenttype==0){
			 sql.append("and urgenttype(+)=? ");
			 params.add(urgenttype);
		 }
		 if(StringUtils.isNotBlank(transfername)){
			 sql.append("and transfername(+)=? ");
			 params.add(transfername);
		 }
		 if(drugtype>0){
			 sql.append("and drugtype(+)=? ");
			 params.add(drugtype);
		 }
		 if(StringUtils.isNotBlank(province)){
			 sql.append("and province(+)=? ");
			 params.add(province);
		 }
		 if(StringUtils.isNotBlank(city)){
			 sql.append("and city(+)=? ");
			 params.add(city);
		 }
		 
		 if(StringUtils.isNotBlank(district)){
			 sql.append("and district(+)=? ");
			 params.add(district);
		 }
		 sql.append("group by dt.data_date,drugtypecn order by dt.data_date");
		 
		 System.out.println("xxx"+sql.toString());
		 return mJdbcTemplate.queryForList(sql.toString(), params.toArray());
	 }

}
